<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;

class Tifykhhxzkcx extends Common
{

	//列表--非常规和进场
    public function index()
    {
        $where = [];
        $jxsbh = $this->request->get('jxsbh');
        $this->assign('jxsbh',$jxsbh);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $fq = $this->request->get('fq');
        $this->assign('fq',$fq);
        $sf = $this->request->get('sf');
        $this->assign('sf',$sf);
        $cs = $this->request->get('cs');
        $this->assign('cs',$cs);

        if($jxsbh){
            $where['sn'] = ['like',"%{$jxsbh}%"];
        }
        if($jxsmc){
            $where['title'] = ['like',"%{$jxsmc}%"];
        }
        if($fq){
            $fqids = db('cg_market_zoning')->where('title','like',"%{$fq}%")->column('id');
            $where['zoning'] = ['in',$fqids];
        }
        if($sf){
            $sfids = db('cg_market_provice')->where('title','like',"%{$sf}%")->column('id');
            $where['provice'] = ['in',$sfids];
        }
        if($cs){
            $csids = db('cg_market_city')->where('title','like',"%{$cs}%")->column('id');
            $where['city'] = ['in',$csids];
        }

        $count = db('cg_jxs')
            ->where($where)
            ->count();
        $data = db('cg_jxs')
            ->where($where)
            ->paginate(10,$count);
//        echo Db::table('cg_ti')->getLastSql();
        $page = $data->render();
        $data = $data->items();
        $maxpage=ceil($count/10);
        foreach($data as $k=>$v){
            $v['zoning'] = db('cg_market_zoning')->where('id',$v['zoning'])->value('title');
            $v['provice'] = db('cg_market_provice')->where('id',$v['provice'])->value('title');
            $v['city'] = db('cg_market_city')->where('id',$v['city'])->value('title');
            $v['ypz'] = db('cg_ti_list t')
                ->join('cg_ti t2','t.ti_id=t2.id')
                ->where('t2.jxs_id',$v['id'])
                ->sum('t.pizhunhanshui');//已批准
            $v['ypz'] = round($v['ypz'],2);

            $v['yhx'] = db('cg_ti_hexiao_list t')
                ->join('cg_ti_hexiao t2','t.hx_id=t2.id')
                ->where(['t2.jxs_id'=>$v['id'],'t2.status'=>9])
                ->sum('t.shijitourujine');//已核销
            $v['yhx'] = round($v['yhx'],2);

            //申请中
            $v['sqz'] = db('cg_ti_hexiao_list t')
                ->join('cg_ti_hexiao t2','t.hx_id=t2.id')
                ->where(['t2.jxs_id'=>$v['id'],'t2.status'=>['in',[1,2,3,4,5]]]) //已打回，区域领导审批，拆分人审批，集团领导审批，已归档
                ->sum('t.shijitourujine');
            $v['sqz'] = round($v['sqz'],2);

            //未申请
            $v['wsq'] = db('cg_ti_hexiao_list t')
                ->join('cg_ti_hexiao t2','t.hx_id=t2.id')
                ->where(['t2.jxs_id'=>$v['id'],'t2.status'=>0]) //草稿
                ->sum('t.shijitourujine');
            $v['wsq'] = round($v['wsq'],2);

            //不予核销
            $v['pizhunhanshui'] = db('cg_ti_list t')
                ->join('cg_ti t2','t.ti_id=t2.id')
                ->where(['t2.jxs_id'=>$v['id'],'t.hx_status'=>2]) //不予核销
                ->sum('t.pizhunhanshui');
            $v['yihexiao2'] = db('cg_ti_list t')
                ->join('cg_ti t2','t.ti_id=t2.id')
                ->where(['t2.jxs_id'=>$v['id'],'t.hx_status'=>2]) //不予核销
                ->sum('t.hx_yihexiao2');
            $v['byhx'] = $v['pizhunhanshui']-$v['yihexiao2'];

            $v['byhx'] = round($v['byhx'],2);

            $data[$k] = $v;
        }

        $this->assign('data',$data);
        $this->assign('page',$page);
		$this->assign('maxpage',$maxpage);
        return $this->fetch();
    }

    //列表--常规
    public function cgindex()
    {
        $where = [];
        $jxsbh = $this->request->get('jxsbh');
        $this->assign('jxsbh',$jxsbh);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $fq = $this->request->get('fq');
        $this->assign('fq',$fq);
        $sf = $this->request->get('sf');
        $this->assign('sf',$sf);
        $cs = $this->request->get('cs');
        $this->assign('cs',$cs);
        $where = [];
        $count = db('cg_jxs')
            ->where($where)
            ->count();
        $data = db('cg_jxs')
            ->where($where)
            ->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['zoning'] = db('cg_market_zoning')->where('id',$v['zoning'])->value('title');
            $v['provice'] = db('cg_market_provice')->where('id',$v['provice'])->value('title');
            $v['city'] = db('cg_market_city')->where('id',$v['city'])->value('title');

            //已批准
            $v['ypz'] = round(db('ti_apply_detail t')->join('ti_apply t2','t2.id=t.apply_id')->where('t2.jxs_id',$v['id'])->sum('pifuzonge'),2);

            //已核销
            /*$t = db('ti_hexiao_detail t')->join('ti_apply_detail t2','t2.id=t.detail_id')->join('ti_apply t3','t3.id=t2.apply_id')->where(['t3.jxs_id'=>$v['id']])->field(['hexiao_weishui','hexiao_shuilv','t.dikou'])->select();
            $yhx = 0;
            foreach($t as $k2=>$v2){
                $yhx += (($v2['dikou']==1)?$v2['hexiao_weishui']:($v2['hexiao_weishui']*(1+$v2['hexiao_shuilv']/100)));
            }
            $v['yhx'] = round($yhx,2);*/
            $v['yhx'] = 0;
            $v['sqz'] = 0;
            $v['wsq'] = 0;
            $v['byhx'] = 0;


            $data[$k] = $v;
        }
        $this->assign([
            'page'=>$page,
            'data'=>$data,
        ]);
        return $this->fetch();
    }

    //已批准页面
    public function approvedPage(){
        $id = $this->request->param('id');
        $title = db('cg_jxs')->where('id',$id)->value('title');
        $this->assign('title',$title);

        $fq = db('cg_jxs t')
            ->join('cg_market_zoning t2','t2.id=t.zoning')
            ->where('t.id',$id)
            ->value('t2.title');
        $this->assign('fq',$fq);

        $sf = db('cg_jxs t')
            ->join('cg_market_provice t2','t2.id=t.provice')
            ->where('t.id',$id)
            ->value('t2.title');
        $this->assign('sf',$sf);

        $cs = db('cg_jxs t')
            ->join('cg_market_city t2','t2.id=t.city')
            ->where('t.id',$id)
            ->value('t2.title');
        $this->assign('cs',$cs);

        $count = db('cg_ti t')
            ->join('cg_ti_list t2','t2.ti_id=t.id')
            ->where(['t.jxs_id'=>$id])
            ->field('t.sn,t2.sn,t2.id,t2.net_id,t2.brand,t2.category,t2.series,t2.sku,t2.product,t2.start,t2.end,t2.feetype,t2.pizhunhanshui')
            ->count();
        $mx = db('cg_ti t')
            ->join('cg_ti_list t2','t2.ti_id=t.id')
            ->where(['t.jxs_id'=>$id])
            ->field('t.type_id,t.sn tsn,t2.sn t2sn,t2.id,t2.net_id,t2.brand,t2.category,t2.series,t2.sku,t2.product,t2.start,t2.end,t2.feetype,t2.pizhunhanshui,tm_id')
            ->paginate(10,$count);
        $page = $mx->render();
        $mx->items();
        foreach($mx as $k=>$v){
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')
                ->join('cg_net t2','t2.system_id=t.id')
                ->where('t2.id',$v['net_id'])
                ->value('t.title');
            if($v['brand'])
                $v['brand_name'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            elseif($v['category'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->where('t.id',$v['category'])->value('brand_name');
            elseif($v['series'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->where('t.id',$v['series'])->value('brand_name');
            elseif($v['sku'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t3','t3.series_id=t.id')
                    ->where('t.id',$v['sku'])->value('brand_name');
            elseif($v['product'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['product'])->value('brand_name');
            elseif($v['tm_id'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['tm_id'])->value('brand_name');
            $v['fykm3'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $mx[$k] = $v;
        }
        $this->assign('mx',$mx);
        $this->assign('page',$page);

        return $this->fetch();
    }
    //已核销页面
    public function vorifiedPage(){
        $id = $this->request->param('id');
        $title = db('cg_jxs')->where('id',$id)->value('title');
        $this->assign('title',$title);

        //已核销明细
        $count = db('cg_ti_hexiao_list t')
            ->join('cg_ti_hexiao t2','t2.id=t.hx_id')
            ->join('cg_ti_list t3','t3.id=t.list_id')
            ->where('t2.jxs_id',$id)
            ->count();
        $mx = db('cg_ti_hexiao_list t')
            ->join('cg_ti_hexiao t2','t2.id=t.hx_id')
            ->join('cg_ti_list t3','t3.id=t.list_id')
            ->where('t2.jxs_id',$id)
            ->field('t2.sn t2sn,t.sn tsn,t2.id,t3.net_id,t3.brand,t3.category,t3.series,t3.sku,t3.product,t3.tm_id,t.ptime,t3.
            feetype,t.shijitourujine,t3.f1')
            ->paginate(10,$count);
        $page = $mx->render();
        $mx = $mx->items();
        foreach($mx as $k=>$v){
            $v['net_title'] = db('cg_net')
                ->where('id',$v['net_id'])
                ->value('title');
            $v['system_title'] = db('cg_system t')
                ->join('cg_net t2','t2.system_id=t.id')
                ->where('t2.id',$v['net_id'])
                ->value('t.title');
            if($v['brand'])
                $v['brand_name'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            elseif($v['category'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->where('t.id',$v['category'])->value('brand_name');
            elseif($v['series'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->where('t.id',$v['series'])->value('brand_name');
            elseif($v['sku'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t3','t3.series_id=t.id')
                    ->where('t.id',$v['sku'])->value('brand_name');
            elseif($v['product'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['product'])->value('brand_name');
            elseif($v['tm_id'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['tm_id'])->value('brand_name');
            elseif($v['f1'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['f1'])->value('brand_name');
            $v['fykm3'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $mx[$k] = $v;
        }
        $this->assign('mx',$mx);
        $this->assign('page',$page);

        return $this->fetch();
    }
    //申请中页面
    public function applyingPage(){
        $id = $this->request->param('id');
        $title = db('cg_jxs')->where('id',$id)->value('title');
        $this->assign('title',$title);

        $count = db('cg_ti_hexiao t')
            ->join('cg_ti_hexiao_list t2','t2.hx_id=t.id')
            ->join('cg_ti_list t3','t3.id=t2.list_id')
            ->where(['t.jxs_id'=>$id,'t.status'=>['in',[1,2,3,4,5]]])
            ->count();
        $mx = db('cg_ti_hexiao t')
            ->join('cg_ti_hexiao_list t2','t2.hx_id=t.id')
            ->join('cg_ti_list t3','t3.id=t2.list_id')
            ->where([
                't.jxs_id'=>$id,
                't.status'=>['in',[1,2,3,4,5]]
            ])
            ->field([
                't.sn'=>'tsn',
                't.id'=>'id',
                't2.sn'=>'t2sn',
                't3.net_id',
                't3.brand',
                't3.category',
                't3.series',
                't3.sku',
                't3.product',
                't3.tm_id',
                't.ctime',
                't3.feetype',
                't2.shijitourujine',
            ])
            ->paginate(10,$count);
        $page = $mx->render();
        $mx = $mx->items();
        foreach($mx as $k=>$v){
            $v['net_title'] = db('cg_net')
                ->where('id',$v['net_id'])
                ->value('title');
            $v['system_title'] = db('cg_system t')
                ->join('cg_net t2','t2.system_id=t.id')
                ->where('t2.id',$v['net_id'])
                ->value('t.title');
            if($v['brand'])
                $v['brand_name'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            elseif($v['category'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->where('t.id',$v['category'])->value('brand_name');
            elseif($v['series'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->where('t.id',$v['series'])->value('brand_name');
            elseif($v['sku'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t3','t3.series_id=t.id')
                    ->where('t.id',$v['sku'])->value('brand_name');
            elseif($v['product'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['product'])->value('brand_name');
            elseif($v['tm_id'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['tm_id'])->value('brand_name');
            elseif($v['f1'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['f1'])->value('brand_name');
            $v['fykm3'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $mx[$k] = $v;
        }
        $this->assign('mx',$mx);
        $this->assign('page',$page);
        return $this->fetch();
    }

    //不予核销
    public function novorifiedpage(){
        $id = $this->request->param('id');
        $title = db('cg_jxs')->where('id',$id)->value('title');
        $this->assign('title',$title);
        $fq = db('cg_jxs t')
            ->join('cg_market_zoning t2','t2.id=t.zoning')
            ->where('t.id',$id)
            ->value('t2.title');
        $this->assign('fq',$fq);

        $sf = db('cg_jxs t')
            ->join('cg_market_provice t2','t2.id=t.provice')
            ->where('t.id',$id)
            ->value('t2.title');
        $this->assign('sf',$sf);

        $cs = db('cg_jxs t')
            ->join('cg_market_city t2','t2.id=t.city')
            ->where('t.id',$id)
            ->value('t2.title');
        $this->assign('cs',$cs);

        $count = db('cg_ti t')
            ->join('cg_ti_list t2','t2.ti_id=t.id')
            ->where([
                't.jxs_id'=>$id,
                't2.hx_status'=>2
                ])
            ->count();
        $mx = db('cg_ti t')
            ->join('cg_ti_list t2','t2.ti_id=t.id')
            ->where([
                't.jxs_id'=>$id,
                't2.hx_status'=>2
            ])
            ->field([
                't.type_id',
                't.sn tsn',
                't2.sn'=>'t2sn',
                't2.id',
                't2.net_id',
                't2.brand',
                't2.category',
                't2.series',
                't2.sku',
                't2.product',
                't2.start',
                't2.end',
                't2.feetype',
                't2.pizhunhanshui',
                't2.tm_id',
                't2.hx_yihexiao2',
            ])
            ->paginate(10,$count);
        $page = $mx->render();
        $mx->items();
        foreach($mx as $k=>$v){
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')
                ->join('cg_net t2','t2.system_id=t.id')
                ->where('t2.id',$v['net_id'])
                ->value('t.title');
            if($v['brand'])
                $v['brand_name'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            elseif($v['category'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->where('t.id',$v['category'])->value('brand_name');
            elseif($v['series'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->where('t.id',$v['series'])->value('brand_name');
            elseif($v['sku'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t3','t3.series_id=t.id')
                    ->where('t.id',$v['sku'])->value('brand_name');
            elseif($v['product'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['product'])->value('brand_name');
            elseif($v['tm_id'])
                $v['brand_name'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t.id')
                    ->join('pro_sku t4','t4.series_id=t.id')
                    ->join('pro_products t5','t5.sku_id=t.id')
                    ->where('t.id',$v['tm_id'])->value('brand_name');
            $v['fykm3'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $v['yue'] = $v['pizhunhanshui']-$v['hx_yihexiao2'];
            $mx[$k] = $v;
        }
        $this->assign('mx',$mx);
        $this->assign('page',$page);
        return $this->fetch();
    }

    //对账单--非常规和进场
    public function duizhangdan(){
        $id = $this->request->param('id');
        $jxs = db('cg_jxs')->where('id',$id)->find();
        $this->assign('jxs',$jxs);

        //已批准
        $ypz = db('cg_ti_list t')
            ->join('cg_ti t2','t2.id=t.ti_id')
            ->where('t2.jxs_id',$id)
            ->sum('t.pizhunhanshui');
        $ypz = round($ypz,2);
        $this->assign('ypz',$ypz);
        //已核销
        $yhx = db('cg_ti_list t')
            ->join('cg_ti t2','t2.id=t.ti_id')
            ->where('t2.jxs_id',$id)
            ->sum('t.hx_yihexiao2');
        $yhx = round($yhx,2);
        $this->assign('yhx',$yhx);
        //申请中
        $sqz = db('cg_ti_hexiao t')
            ->join('cg_ti_hexiao_list t2','t2.hx_id=t.id')
            ->where(['t.jxs_id'=>$id,'t.status'=>['in',[1,2,3,4,5]]])
            ->sum('t2.shijitourujine');
        $this->assign('sqz',$sqz);

        $wsq = db('cg_ti_hexiao_list t')
            ->join('cg_ti_hexiao t2','t.hx_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t2.status'=>0]) //草稿
            ->sum('t.shijitourujine');
        $wsq = round($wsq,2);
        $this->assign('wsq',$wsq);

        //不予核销
        $pizhunhanshui = db('cg_ti_list t')
            ->join('cg_ti t2','t.ti_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.hx_status'=>2]) //不予核销
            ->sum('t.pizhunhanshui');
        $yihexiao2 = db('cg_ti_list t')
            ->join('cg_ti t2','t.ti_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.hx_status'=>2]) //不予核销
            ->sum('t.hx_yihexiao2');
        $byhx = $pizhunhanshui-$yihexiao2;
        $byhx = round($byhx,2);
        $this->assign('byhx',$byhx);

        //已支付
        $yzf = db('cg_ti_hexiao_zhifu t')
            ->join('cg_ti_hexiao t2','t2.id=t.hx_id')
            ->where('t2.jxs_id',$id)
            ->sum('bczfje');
        $this->assign('yzf',$yzf);

        //待支付
        $dzf = $yhx-$yzf;
        $this->assign('dzf',$dzf);

        return $this->fetch();
    }

    //导出excel--非常规和进场
    public function export(){
        $id = $this->request->param('id');
        $jxs = db('cg_jxs')->where('id',$id)->find();

        //已批准
        $ypz = db('cg_ti_list t')
            ->join('cg_ti t2','t2.id=t.ti_id')
            ->where('t2.jxs_id',$id)
            ->sum('t.pizhunhanshui');
        $ypz = round($ypz,2);
        //已核销
        $yhx = db('cg_ti_list t')
            ->join('cg_ti t2','t2.id=t.ti_id')
            ->where('t2.jxs_id',$id)
            ->sum('t.hx_yihexiao2');
        $yhx = round($yhx,2);
        //申请中
        $sqz = db('cg_ti_hexiao t')
            ->join('cg_ti_hexiao_list t2','t2.hx_id=t.id')
            ->where(['t.jxs_id'=>$id,'t.status'=>['in',[1,2,3,4,5]]])
            ->sum('t2.shijitourujine');
        $sqz = round($sqz,2);

        $wsq = db('cg_ti_hexiao_list t')
            ->join('cg_ti_hexiao t2','t.hx_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t2.status'=>0]) //草稿
            ->sum('t.shijitourujine');
        $wsq = round($wsq,2);

        //不予核销
        $pizhunhanshui = db('cg_ti_list t')
            ->join('cg_ti t2','t.ti_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.hx_status'=>2]) //不予核销
            ->sum('t.pizhunhanshui');
        $pizhunhanshui = round($pizhunhanshui,2);

        $yihexiao2 = db('cg_ti_list t')
            ->join('cg_ti t2','t.ti_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.hx_status'=>2]) //不予核销
            ->sum('t.hx_yihexiao2');
        $byhx = $pizhunhanshui-$yihexiao2;
        $byhx = round($byhx,2);

        //已支付
        $yzf = db('cg_ti_hexiao_zhifu t')
            ->join('cg_ti_hexiao t2','t2.id=t.hx_id')
            ->where('t2.jxs_id',$id)
            ->sum('bczfje');

        //待支付
        $dzf = $yhx-$yzf;

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("对账单");

        $as->mergeCells('A1:G1');
        $as->setCellValue('A1','对账单');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:G2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $as->mergeCells('A3:G3');
        $as->setCellValue('A3',$jxs['title'].'(编号：'.$jxs['sn'].')');
        $as->mergeCells('A4:G4');
        $as->setCellValue('A4','营业地址：'.$jxs['addr'].'  联系人：'.$jxs['contact1'].' 联系电话：'.$jxs['mobile1']);

        $as->mergeCells('A5:G5');
        $as->setCellValue('A5','下述金额为含税金额：人民币元');
        $as->getStyle('A5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        //标题
        $as->getStyle('A6:G7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A6:G7')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->mergeCells('A6:A7');
        $as->setCellValue('A6','已批准');
        $as->mergeCells('B6:B7');
        $as->setCellValue('B6','已核销');
        $as->mergeCells('C6:D6');
        $as->setCellValue('C6','未核销');
        $as->mergeCells('E6:E7');
        $as->setCellValue('E6','不予核销');
        $as->mergeCells('F6:F7');
        $as->setCellValue('F6','已支付');
        $as->mergeCells('G6:G7');
        $as->setCellValue('G6','待支付');
        $as->setCellValue('C7','申请中');
        $as->setCellValue('D7','未申请');

        $n = 8;
        $as->setCellValue('A'.$n,$ypz);
        $as->setCellValue('B'.$n,$yhx);
        $as->setCellValue('C'.$n,$sqz);
        $as->setCellValue('D'.$n,$wsq);
        $as->setCellValue('E'.$n,$byhx);
        $as->setCellValue('F'.$n,$yzf);
        $as->setCellValue('G'.$n,$dzf);
        $t = $n;
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'补充');
        $as->getStyle('A'.$t)->getFont()->setBold(true);
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'经销商必须在拿到申请后才可以作费用垫付核销。广州销路通软件有限公司将不知福没有申请号的费用核销。');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'因为活动可能存在变动或取消，请经销商以最新的对账信息为准，恕不另行通知');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'如对以上对账单有任何疑问，请尽快与我方联系确认。');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'本对账单最终解释权归本公司所有。');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->getStyle('A'.$t)->getAlignment()->setHorizontal('right');
        $as->setCellValue('A'.$t,date('Y-m-d'));

        $n = $as->getHighestRow();
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->getStyle('A1:G'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=对账单.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //对账单2--非常规和进场
    public function duizhangdan2(){
        $id = $this->request->param('id');
        $jxs = db('cg_jxs')->where('id',$id)->find();
        $this->assign('jxs',$jxs);

        //查询明细-按照互动
        $sn = $this->request->get('sn');
        $title = $this->request->get('title');
        $this->assign(['title'=>$title,'sn'=>$sn]);
        $where = [];
        $where['jxs_id'] = $id;
        if($sn){
            $where['sn'] = ['like',"%{$sn}%"];
        }
        if($title){
            $where['title'] = ['like',"%{$title}%"];
        }

        $mx = db('cg_ti t')
            ->where($where)
            ->field(['id','title','sn'])
            ->select();
//        echo db('cg_ti')->getLastSql();
        $ypz = 0;
        $yhx = 0;
        $sqz = 0;
        $wsq = 0;
        $byhx = 0;
        $yzf = 0;
        $dzf = 0;
        foreach ($mx as $k=>$v){
            $v['ypz'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('pizhunhanshui');
            $ypz += $v['ypz'];
            $v['ypz'] = round($v['ypz'],2);
            $v['yhx'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_yihexiao2');
            $yhx = $v['yhx'];
            $v['yhx'] = round($v['yhx'],2);
            $v['sqz'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_sqz');
            $sqz = $v['sqz'];
            $v['sqz'] = round($v['sqz'],2);
            $v['wsq'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_wsq');
            $wsq = $v['wsq'];
            $v['wsq'] = round($v['wsq'],2);
            $v['byhx'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_byhx');
            $byhx = $v['byhx'];
            $v['byhx'] = round($v['byhx'],2);
            $v['yzf'] = 0;
            $v['dzf'] = 0;
            $yzf = 0;
            $dzf = 0;
            $mx[$k] = $v;
        }
        $this->assign([
            'ypz' => $ypz,
            'yhx' => $yhx,
            'sqz' => $sqz,
            'wsq' => $wsq,
            'byhx' => $byhx,
            'yzf' => $yzf,
            'dzf' => $dzf,
            'mx'=>$mx,
        ]);

        return $this->fetch();
    }
    //导出excel--非常规和进场
    public function export2(){
        $id = $this->request->param('id');
        $jxs = db('cg_jxs')->where('id',$id)->find();

        //查询明细-按照互动
        $sn = $this->request->get('sn');
        $title = $this->request->get('title');
        $this->assign(['title'=>$title,'sn'=>$sn]);
        $where = [];
        $where['jxs_id'] = $id;
        if($sn){
            $where['sn'] = ['like',"%{$sn}%"];
        }
        if($title){
            $where['title'] = ['like',"%{$title}%"];
        }

        $mx = db('cg_ti t')
            ->where($where)
            ->field(['id','title','sn'])
            ->select();
//        echo db('cg_ti')->getLastSql();
        $ypz = 0;
        $yhx = 0;
        $sqz = 0;
        $wsq = 0;
        $byhx = 0;
        $yzf = 0;
        $dzf = 0;
        foreach ($mx as $k=>$v){
            $v['ypz'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('pizhunhanshui');
            $ypz += $v['ypz'];
            $v['ypz'] = round($v['ypz'],2);
            $v['yhx'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_yihexiao2');
            $yhx = $v['yhx'];
            $v['yhx'] = round($v['yhx'],2);
            $v['sqz'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_sqz');
            $sqz = $v['sqz'];
            $v['sqz'] = round($v['sqz'],2);
            $v['wsq'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_wsq');
            $wsq = $v['wsq'];
            $v['wsq'] = round($v['wsq'],2);
            $v['byhx'] = db('cg_ti_list')->where('ti_id',$v['id'])->sum('hx_byhx');
            $byhx = $v['byhx'];
            $v['byhx'] = round($v['byhx'],2);
            $v['yzf'] = 0;
            $v['dzf'] = 0;
            $yzf = 0;
            $dzf = 0;
            $mx[$k] = $v;
        }


        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("对账单");

        $as->mergeCells('A1:I1');
        $as->setCellValue('A1','对账单');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:I2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $as->mergeCells('A3:I3');
        $as->setCellValue('A3',$jxs['title'].'(编号：'.$jxs['sn'].')');
        $as->mergeCells('A4:I4');
        $as->setCellValue('A4','营业地址：'.$jxs['addr'].'  联系人：'.$jxs['contact1'].' 联系电话：'.$jxs['mobile1']);

        $as->mergeCells('A5:I5');
        $as->setCellValue('A5','下述金额为含税金额：人民币元');
        $as->getStyle('A5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        //标题
        $as->getStyle('A6:I7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A6:I7')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->mergeCells('A6:A7');
        $as->setCellValue('A6','活动编号');
        $as->mergeCells('B6:B7');
        $as->setCellValue('B6','活动主题');
        $as->mergeCells('C6:C7');
        $as->setCellValue('C6','已批准');
        $as->mergeCells('D6:D7');
        $as->setCellValue('D6','已核销');
        $as->mergeCells('E6:F6');
        $as->setCellValue('E6','未核销');
        $as->mergeCells('G6:G7');
        $as->setCellValue('G6','不予核销');
        $as->mergeCells('H6:H7');
        $as->setCellValue('H6','已支付');
        $as->mergeCells('I6:I7');
        $as->setCellValue('I6','待支付');
        $as->setCellValue('E7','申请中');
        $as->setCellValue('F7','未申请');

        foreach($mx as $k=>$v){
            $n = 8+$k;
            $as->setCellValue('A'.$n,$v['sn']);
            $as->setCellValue('B'.$n,''.$v['title']);
            $as->setCellValue('C'.$n,$v['ypz']);
            $as->setCellValue('D'.$n,$v['yhx']);
            $as->setCellValue('E'.$n,$v['sqz']);
            $as->setCellValue('F'.$n,$v['wsq']);
            $as->setCellValue('G'.$n,$v['byhx']);
            $as->setCellValue('H'.$n,$v['yzf']);
            $as->setCellValue('I'.$n,$v['dzf']);
        }
        $t = 8+count($mx);
        $as->mergeCells('A'.$t.':B'.$t);
        $as->setCellValue('A'.$t,'总计');
        $as->getStyle('A'.$t)->getAlignment()->setHorizontal('right');
        $as->setCellValue('C'.$t,$ypz);
        $as->setCellValue('D'.$t,$yhx);
        $as->setCellValue('E'.$t,$sqz);
        $as->setCellValue('F'.$t,$wsq);
        $as->setCellValue('G'.$t,$byhx);
        $as->setCellValue('H'.$t,$yzf);
        $as->setCellValue('I'.$t,$dzf);
        $t++;
        $as->mergeCells('A'.$t.':I'.$t);
        $as->setCellValue('A'.$t,'补充');
        $as->getStyle('A'.$t)->getFont()->setBold(true);
        $t++;
        $as->mergeCells('A'.$t.':I'.$t);
        $as->setCellValue('A'.$t,'经销商必须在拿到申请后才可以作费用垫付核销。广州销路通软件有限公司将不知福没有申请号的费用核销。');
        $t++;
        $as->mergeCells('A'.$t.':I'.$t);
        $as->setCellValue('A'.$t,'因为活动可能存在变动或取消，请经销商以最新的对账信息为准，恕不另行通知');
        $t++;
        $as->mergeCells('A'.$t.':I'.$t);
        $as->setCellValue('A'.$t,'如对以上对账单有任何疑问，请尽快与我方联系确认。');
        $t++;
        $as->mergeCells('A'.$t.':I'.$t);
        $as->setCellValue('A'.$t,'本对账单最终解释权归本公司所有。');
        $t++;
        $as->mergeCells('A'.$t.':I'.$t);
        $t++;
        $as->mergeCells('A'.$t.':I'.$t);
        $as->getStyle('A'.$t)->getAlignment()->setHorizontal('right');
        $as->setCellValue('A'.$t,date('Y-m-d'));

        $n = $as->getHighestRow();
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->getStyle('A1:I'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=对账单.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    //对账单--常规
    public function duizhangdancg(){
        $id = $this->request->param('id');
        $jxs = db('cg_jxs')->where('id',$id)->find();
        $this->assign('jxs',$jxs);

        //已批准
        $ypz = db('ti_apply_detail t')
            ->join('ti_apply t2','t2.id=t.apply_id')
            ->where('t2.jxs_id',$id)
            ->sum('t.pifuzonge');
        $ypz = round($ypz,2);
        $this->assign('ypz',$ypz);
        //已核销
        $yhx = 0;
        $yhx = round($yhx,2);
        $this->assign('yhx',$yhx);
        //申请中
        $sqz = 0;
        $this->assign('sqz',$sqz);

        $wsq = 0;
        $wsq = round($wsq,2);
        $this->assign('wsq',$wsq);

        //不予核销
        $pizhunhanshui = db('ti_apply_detail t')
            ->join('ti_apply t2','t.apply_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.buyu_he'=>1]) //不予核销
            ->sum('t.pifuzonge');
        $yihexiao2 = db('ti_apply_detail t')
            ->join('ti_apply t2','t.apply_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.buyu_he'=>1]) //不予核销
            ->sum('t.hx_yihexiao2');
        $byhx = $pizhunhanshui-$yihexiao2;
        $byhx = round($byhx,2);
        $this->assign('byhx',$byhx);

        //已支付
        $yzf = db('cg_ti_hexiao_zhifu t')
            ->join('cg_ti_hexiao t2','t2.id=t.hx_id')
            ->where('t2.jxs_id',$id)
            ->sum('bczfje');
        $this->assign('yzf',$yzf);

        //待支付
        $dzf = $yhx-$yzf;
        $this->assign('dzf',$dzf);

        return $this->fetch();
    }

    //导出excel--常规
    public function exporthcg(){
        $id = $this->request->param('id');
        $jxs = db('cg_jxs')->where('id',$id)->find();

        //已批准
        $ypz = db('ti_apply_detail t')
            ->join('ti_apply t2','t2.id=t.apply_id')
            ->where('t2.jxs_id',$id)
            ->sum('t.pifuzonge');
        $ypz = round($ypz,2);
        //已核销
        $yhx = db('ti_apply_detail t')
            ->join('ti_apply t2','t2.id=t.apply_id')
            ->where('t2.jxs_id',$id)
            ->sum('t.hx_yihexiao2');
        $yhx = round($yhx,2);
        //申请中
        $sqz = 0;
        $t = db('ti_hexiao_apply t')
            ->join('ti_hexiao_detail t2','t2.hexiao_id=t.id')
            ->where(['t.jxs_id'=>$id,'t.state'=>['in',[1,3,4,5]]])
            ->field('t2.hexiao_weishui,hexiao_shuilv,dikou')
            ->select();
        foreach($t as $k=>$v){
            $sqz += $v['dikou']==1?$v['hexiao_weishui']:($v['hexiao_weishui']*(1+$v['hexiao_shuilv']/100));
        }
        $sqz = round($sqz,2);

        $wsq = 0;
        $t = db('ti_hexiao_apply t')
            ->join('ti_hexiao_detail t2','t2.hexiao_id=t.id')
            ->where(['t.jxs_id'=>$id,'t.state'=>2])
            ->field('t2.hexiao_weishui,hexiao_shuilv,dikou')
            ->select();
        foreach($t as $k=>$v){
            $wsq += $v['dikou']==1?$v['hexiao_weishui']:($v['hexiao_weishui']*(1+$v['hexiao_shuilv']/100));
        }
        $wsq = round($wsq,2);

        //不予核销
        $pizhunhanshui = db('ti_apply_detail t')
            ->join('ti_apply t2','t.apply_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.buyu_he'=>1]) //不予核销
            ->sum('t.hexiao');
        $pizhunhanshui = round($pizhunhanshui,2);

        $yihexiao2 = db('ti_apply_detail t')
            ->join('ti_apply t2','t.apply_id=t2.id')
            ->where(['t2.jxs_id'=>$id,'t.buyu_he'=>1]) //不予核销
            ->sum('t.hx_yihexiao2');
        $byhx = $pizhunhanshui-$yihexiao2;
        $byhx = round($byhx,2);

        //已支付
        $yzf = db('ti_hexiao_zhifu t')
            ->join('ti_hexiao_apply t2','t2.id=t.hx_id')
            ->where('t2.jxs_id',$id)
            ->sum('bczfje');

        //待支付
        $dzf = $yhx-$yzf;

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("对账单");

        $as->mergeCells('A1:G1');
        $as->setCellValue('A1','对账单');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:G2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $as->mergeCells('A3:G3');
        $as->setCellValue('A3',$jxs['title'].'(编号：'.$jxs['sn'].')');
        $as->mergeCells('A4:G4');
        $as->setCellValue('A4','营业地址：'.$jxs['addr'].'  联系人：'.$jxs['contact1'].' 联系电话：'.$jxs['mobile1']);

        $as->mergeCells('A5:G5');
        $as->setCellValue('A5','下述金额为含税金额：人民币元');
        $as->getStyle('A5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        //标题
        $as->getStyle('A6:G7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A6:G7')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->mergeCells('A6:A7');
        $as->setCellValue('A6','已批准');
        $as->mergeCells('B6:B7');
        $as->setCellValue('B6','已核销');
        $as->mergeCells('C6:D6');
        $as->setCellValue('C6','未核销');
        $as->mergeCells('E6:E7');
        $as->setCellValue('E6','不予核销');
        $as->mergeCells('F6:F7');
        $as->setCellValue('F6','已支付');
        $as->mergeCells('G6:G7');
        $as->setCellValue('G6','待支付');
        $as->setCellValue('C7','申请中');
        $as->setCellValue('D7','未申请');

        $n = 8;
        $as->setCellValue('A'.$n,$ypz);
        $as->setCellValue('B'.$n,$yhx);
        $as->setCellValue('C'.$n,$sqz);
        $as->setCellValue('D'.$n,$wsq);
        $as->setCellValue('E'.$n,$byhx);
        $as->setCellValue('F'.$n,$yzf);
        $as->setCellValue('G'.$n,$dzf);
        $t = $n;
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'补充');
        $as->getStyle('A'.$t)->getFont()->setBold(true);
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'经销商必须在拿到申请后才可以作费用垫付核销。广州销路通软件有限公司将不知福没有申请号的费用核销。');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'因为活动可能存在变动或取消，请经销商以最新的对账信息为准，恕不另行通知');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'如对以上对账单有任何疑问，请尽快与我方联系确认。');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->setCellValue('A'.$t,'本对账单最终解释权归本公司所有。');
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $t++;
        $as->mergeCells('A'.$t.':G'.$t);
        $as->getStyle('A'.$t)->getAlignment()->setHorizontal('right');
        $as->setCellValue('A'.$t,date('Y-m-d'));

        $n = $as->getHighestRow();
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->getStyle('A1:G'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=对账单.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

}
